package dados;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import basicas.Area;
import basicas.Disciplina;
import intefaces.InterfaceArea;

public class DadosArea extends MySQLConnector implements InterfaceArea{

	@Override
	public void inserir(Area area) throws Exception {
		try{
			dbConn = dbConn();
			dbConn.setAutoCommit(false);
	
			String sql = "INSERT INTO Area (nome, descricao) VALUES (?, ?)";
			
			preparedStatementInsert = dbConn.prepareStatement(sql);
			preparedStatementInsert.setString(1, area.getNome());
			preparedStatementInsert.setString(2, area.getDescricao());
			preparedStatementInsert.executeUpdate();
			
			dbConn.commit();
			
		}catch(SQLException ex){
			dbConn.rollback();
		}finally{
			if(preparedStatementInsert != null){
				preparedStatementInsert.close();
			}
			if(dbConn != null){
				dbConn.close();
			}	
		}
		
	}

	@Override
	public void alterar(Area area) throws Exception {
		try{
			dbConn = dbConn();
			dbConn.setAutoCommit(false);
	
			String sql = "UPDATE Area SET nome = ?, descricao = ? WHERE id_area = ?";
			
			preparedStatementInsert = dbConn.prepareStatement(sql);
			preparedStatementInsert.setString(1, area.getNome());
			preparedStatementInsert.setString(2, area.getDescricao());
			preparedStatementInsert.setInt(3, area.getId_area());
			preparedStatementInsert.executeUpdate();
			
			dbConn.commit();
			
		}catch(SQLException ex){
			dbConn.rollback();
		}finally{
			if(preparedStatementInsert != null){
				preparedStatementInsert.close();
			}
			if(dbConn != null){
				dbConn.close();
			}	
		}
		
	}

	@Override
	public void excluir(Area area) throws Exception {
		try{
			dbConn = dbConn();
			dbConn.setAutoCommit(false);
	
			String sql = "DELETE FROM Area WHERE id_area = ?";
			
			preparedStatementInsert = dbConn.prepareStatement(sql);
			preparedStatementInsert.setInt(1, area.getId_area());
			preparedStatementInsert.executeUpdate();
			
			dbConn.commit();
			
		}catch(SQLException ex){
			dbConn.rollback();
		}finally{
			if(preparedStatementInsert != null){
				preparedStatementInsert.close();
			}
			if(dbConn != null){
				dbConn.close();
			}	
		}
		
	}

	@Override
	public List<Area> listarTodas() throws Exception {
		List<Area> lista = new ArrayList<Area>();
		try{
			logar();
			String sql = "SELECT id_area, nome, descricao FROM Area"; 
			ResultSet rs = logar().executeQuery(sql); 
			while(rs.next()){
				Area area = new Area();
				area.setId_area(rs.getInt("id_area"));
				area.setNome(rs.getString("nome"));
				area.setDescricao(rs.getString("descricao"));
				lista.add(area);
			}
			deslogar();
			return lista;
		}catch(Exception ex){
			throw new Exception(ex.getMessage());
		}

	}

	@Override
	public List<Disciplina> listarDisciplinasArea(Area area) throws Exception {
		List<Disciplina> lista = new ArrayList<Disciplina>();
		try{
			logar();
			String sql = "SELECT id_disciplina, nome, descricao FROM Disciplina " +
						 "INNER JOIN Area_Disciplina ON Disciplina.id_disciplina = Area_Disciplina.id_disciplina " +
						 "WHERE Area_Disciplina.id_area = "+area.getId_area(); 
			ResultSet rs = logar().executeQuery(sql); 
			while(rs.next()){
				Disciplina disc = new Disciplina();
				disc.setId_disciplina(rs.getInt("id_disciplina"));
				disc.setNome(rs.getString("nome"));
				disc.setDescricao(rs.getString("descricao"));
				lista.add(disc);
			}
			deslogar();
			return lista;
		}catch(Exception ex){
			throw new Exception(ex.getMessage());
		}
	}

	
	
	
}
